alter table [Contract]
add AgentId int null
go
ALTER TABLE [dbo].[Contract]  WITH CHECK ADD  CONSTRAINT [FK_Contract_User] FOREIGN KEY([AgentId])
REFERENCES [dbo].[User] ([Id])
GO
ALTER TABLE [dbo].[Contract] CHECK CONSTRAINT [FK_Contract_User]
GO
update [Contract]
set [Contract].AgentId = Contact.AgentId
from Contact
where [Contract].ContactId = Contact.ContactId
go
alter table [Contract]
alter column AgentId int not null
go
ALTER PROCEDURE [dbo].[spContract_Search]
(
	@AgentId int,
	@Status varchar(50),
	@ContactName varchar(50)
)  
AS
begin
	select ctr.ContractId, ctr.ContractDate, ctr.ContactId, ctr.TemplateId, ctr.Status,
		ct.FirstName + ' ' + ct.LastName as ContactName,
		ctr.AgentId,
		-- agent - contract creator, not contact owner
		u.FirstName + ' ' + u.LastName as AgentName,
		ctr.ViewDate, ctr.ViewIP, ctr.SignedDate, ctr.SignedIP
    from [Contract] (nolock) ctr left join Contact (nolock) as ct on ctr.ContactId = ct.ContactId
									left join [User] (nolock) as u on ctr.AgentId = u.Id
    where ((ct.AgentId = @AgentId) or (ctr.AgentId = @AgentId) or (@AgentId is null))
		and ((ctr.[Status] = @Status) or (@Status is null))
		and ((ct.LastName LIKE @ContactName + '%') or (@ContactName is null))
end
go
ALTER PROCEDURE [dbo].[spContractEdit]
(
	@ContractId int,
	@ContactId int,
	@TemplateId int,
	@Status varchar(50),
	@AgentId int,
	
	@TemplateFieldId varchar(max),
	@ContactColumnName varchar(max),
	@ContactColumnValue varchar(max)
)
AS
BEGIN
	set nocount on
	set xact_abort on
		
    begin transaction
		if (@ContractId = -1)
		begin
			insert into [Contract]
			(ContactId, TemplateId, [Status], AgentId)
			values (@ContactId, @TemplateId, @Status, @AgentId)
			
			set @ContractId = SCOPE_IDENTITY()
		end
		else begin
			update [Contract]
			set TemplateId = @TemplateId/*, [Status] = @Status*/
			where (ContractId = @ContractId)
		end
		
		delete 
		from ContractField
		where (ContractId = @ContractId)
		
		insert into ContractField
		(ContractId, TemplateFieldId, ContactColumnName, ContactColumnValue)
		select @ContractId, vwTemplate.Item, vwColumnName.Item, vwColumnValue.Item 
		from dbo.fnStringSplit(@TemplateFieldId, ';') as vwTemplate inner join dbo.fnStringSplit(@ContactColumnName, ';') as vwColumnName on vwTemplate.IdItem = vwColumnName.IdItem
																	inner join dbo.fnStringSplit(@ContactColumnValue, ';') as vwColumnValue on vwTemplate.IdItem = vwColumnValue.IdItem

	commit transaction
	
	select @ContractId as ContractId
END
go
